package com.xpgk.mvc.employeesServiceStatistics;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.aop.Before;
import com.jfinal.log.Log;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.xpgk.annotation.Service;
import com.xpgk.constant.ConstantRender;
import com.xpgk.dto.RenderBean;
import com.xpgk.mvc.bcallVisit.BcallVisitService;


@Service(name = EmployeesServiceStatisticsService.serviceName)
public class EmployeesServiceStatisticsService {

	@SuppressWarnings("unused")
	private static final Log log = Log.getLog(EmployeesServiceStatisticsService.class);
	public static final String serviceName = "employeesServiceStatistics";
	
	/**员工服务数据查询
	 * 
	 * @param organIds 机构ids
	 * @param datemin  开始日期
	 * @param datemax  结束日期
	 * @return
	 */
	public List<Record> employeesServiceDateQuery(String organIds, String datemin, String datemax,String range) {
		List<Record> result = new ArrayList<Record>();
		//查询机构名称
		String sqlorganname = "SELECT syso.`name` FROM sys_organization AS syso WHERE syso.ids = ?";
		String organname = Db.findFirst(sqlorganname,organIds).get("name").toString();
		//查询机构员工
		String sqlorganemployees = "SELECT sysu.ids, sysu.`name` FROM sys_user AS sysu WHERE sysu.organizationids = ? ";
		if(range.equals("0")){
			 sqlorganemployees = "SELECT sysu.ids, sysu.`name` FROM sys_user AS sysu WHERE sysu.organizationids = ? and sysu.status = '1'";
		}
		List<Record> employees = Db.find(sqlorganemployees,organIds);
		//查询每个员工的服务数据
		for(Record employee : employees){
			Record record = new Record();
			String employeeids = employee.get("ids").toString();
			String employeename = employee.get("name").toString();
			//店员负责人数
			String sqlchargePersion = "SELECT COUNT(*) AS numbercustomer FROM sb_customer AS sbc LEFT JOIN sb_customer_detail AS sbcd ON sbcd.customerIds = sbc.ids WHERE sbc.organIds = ? AND sbcd.chargePersion = ? ";
			String numbercustomer = Db.findFirst(sqlchargePersion,organIds,employeeids).get("numbercustomer").toString();
			record.set("numbercustomer",numbercustomer+"人");
			String sqlplaster = "SELECT COUNT(*) plaster FROM sb_customer_service AS sbcs WHERE sbcs.organids = ? AND sbcs.plasterServier = ? AND DATE_FORMAT(sbcs.createDate, '%Y-%m-%d') >= ? AND DATE_FORMAT(sbcs.createDate, '%Y-%m-%d') <= ?";
			String plaster = Db.findFirst(sqlplaster,organIds,employeeids,datemin,datemax).get("plaster").toString();
//			String sqljinghu = "SELECT count(*) AS coun, sbcss.partIds AS name FROM sb_customer_service_shaping AS sbcss WHERE sbcss.userIds = ? AND DATE_FORMAT( sbcss.createDate, '%Y-%m-%d' ) >= ? AND DATE_FORMAT( sbcss.createDate, '%Y-%m-%d' ) <= ? GROUP BY sbcss.partIds";
//			List<Record> jinghulist = Db.find(sqljinghu,employeeids,datemin,datemax);
//			String dt = "0次";
//			String yb = "0次";
//			String xt = "0次";
//			String gb = "0次";
//			for(Record jinghu : jinghulist){
//				if(jinghu.get("name").toString().equals("e41f214dc9a04bc691b5674f39de0902")){
//					dt = jinghu.get("coun").toString()+"次";
//				}else if(jinghu.get("name").toString().equals("7b83623e24664b68ab46d68e5255c53f")){
//					yb = jinghu.get("coun").toString()+"次";
//				}else if(jinghu.get("name").toString().equals("92b836d30f494b8388c84a0175839f70")){
//					gb = jinghu.get("coun").toString()+"次";
//				}else if(jinghu.get("name").toString().equals("3d2a59dd84504ff2841e0ebcc1fa339f")){
//					xt = jinghu.get("coun").toString()+"次";
//				}
//			}
			String dt = "0次";
			String yb = "0次";
			String xt = "0次";
			String gb = "0次";
			String bb = "0次";
			String kb = "0次";
			String zfr = "0次";
			String yfr = "0次";
			String ddcs = "0次";
			String gjjs = "0";
			
			String sqldt = "SELECT COUNT(*) dt FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.dt = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			dt = Db.findFirst(sqldt,organIds,employeeids,datemin,datemax).get("dt").toString();
			String sqlyb = "SELECT COUNT(*) yb FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.yb = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			yb = Db.findFirst(sqlyb,organIds,employeeids,datemin,datemax).get("yb").toString();
			String sqlxt = "SELECT COUNT(*) xt FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.xt = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			xt = Db.findFirst(sqlxt,organIds,employeeids,datemin,datemax).get("xt").toString();
			String sqlgb = "SELECT COUNT(*) gb FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.gb = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			gb = Db.findFirst(sqlgb,organIds,employeeids,datemin,datemax).get("gb").toString();
			String sqlbb = "SELECT COUNT(*) bb FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.bb = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			bb = Db.findFirst(sqlbb,organIds,employeeids,datemin,datemax).get("bb").toString();
			String sqlkb = "SELECT COUNT(*) kb FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.kb = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			kb = Db.findFirst(sqlkb,organIds,employeeids,datemin,datemax).get("kb").toString();
			String sqlzfr = "SELECT COUNT(*) zfr FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.zfr = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			zfr = Db.findFirst(sqlzfr,organIds,employeeids,datemin,datemax).get("zfr").toString();
			String sqlyfr = "SELECT COUNT(*) yfr FROM sb_customer_service_shaping AS sbcs LEFT JOIN sb_customer_service scs on scs.ids = sbcs.serviceIds WHERE sbcs.organIds = ? AND sbcs.yfr = ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(scs.startTime, '%Y-%m-%d') <= ?";
			yfr = Db.findFirst(sqlyfr,organIds,employeeids,datemin,datemax).get("yfr").toString();
			//dt = 
			record.set("dt", dt)
			 	  .set("yb", yb)
			 	  .set("xt", xt)
			      .set("gb", gb)
			      .set("bb", bb)
			 	  .set("kb", kb)
			 	  .set("zfr", zfr)
			      .set("yfr", yfr);
			//店员负责顾客到店次数
			String sqlddcs = "select count(a.ids) as ddcs ,sum(lastWeight-weight) as gjjs from sb_customer_service a LEFT JOIN sb_customer_detail b on a.customerID = b.customerIds WHERE b.chargePersion =? and a.dataStatus in ('1','2') and b.dataStatus in ('1','2')  AND DATE_FORMAT(a.startTime, '%Y-%m-%d') >= ? AND DATE_FORMAT(a.startTime, '%Y-%m-%d') <= ?";
			Record ddcsr = Db.findFirst(sqlddcs,employeeids,datemin,datemax);
			if(ddcsr.get("ddcs")!=null){
				ddcs =ddcsr.get("ddcs").toString();
			}
			if(ddcsr.get("gjjs")!=null){
				gjjs = ddcsr.get("gjjs").toString();
			}
			
			record.set("ddcs", ddcs)
				  .set("gjjs", gjjs);
			//店员销售金额查询
			String sqlemployeesales = "SELECT sbpsd.type, sbpsd.price FROM sb_product_sales_detail AS sbpsd WHERE sbpsd.organids = ? AND sbpsd.userID = ? AND DATE_FORMAT(sbpsd.date, '%Y-%m-%d') >= ? AND DATE_FORMAT(sbpsd.date, '%Y-%m-%d') <= ?";
			List<Record> listemployeesales = Db.find(sqlemployeesales,organIds,employeeids,datemin,datemax);
			double loseweightsum = 0;
			double serviceproductsum = 0;
			double otherproductsum = 0;
			if(listemployeesales.size()!=0 && listemployeesales!=null){
				for(Record sellsum : listemployeesales){
					if(sellsum.get("type").toString().equals("455018290a8f4afbbaf29c61904d6406")){
						loseweightsum = loseweightsum+Double.parseDouble(sellsum.get("price").toString());
					}else if(sellsum.get("type").toString().equals("a07f80b6d9064a2e8357f0f87d21a115")){
						serviceproductsum = serviceproductsum + Double.parseDouble(sellsum.get("price").toString());
					}else{
						otherproductsum = otherproductsum + Double.parseDouble(sellsum.get("price").toString());
					}
				}
			}
			record.set("organname", organname)
				  .set("name",employeename)
				  .set("plaster",plaster)
				  .set("loseweightsum", loseweightsum+"元")
				  .set("serviceproductsum", serviceproductsum+"元")
				  .set("otherproductsum", otherproductsum+"元");
			result.add(record);
		}
		return result;
	}
	
	

}